package sql;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import connectionPool.ConnPoolImpl;
import connectionPool.PoolException;

public class SQLs {
	static ConnPoolImpl pool = ConnPoolImpl.getInstance();

	public static synchronized void selectFromKurs(String modul, String nummer,
			String berufsfeld) throws PoolException, SQLException {
		Connection con = pool.getConnection();
		Statement stmt = con.createStatement();

		berufsfeld = berufsfeld.substring(0, 1).toUpperCase()
				+ berufsfeld.substring(1);

		String query = "SELECT BerufsfeldID FROM `santisfeedback`.`berufsfeld` WHERE `Berufsfeld`='"
				+ berufsfeld + "';";
		ResultSet rs = stmt.executeQuery(query);

		if (rs.next()) {
			query = "INSERT INTO `santisfeedback`.`kurs` (`KursID`, `Modul`, `ModulNummer`, `FK_Berufsfeld`) VALUES (NULL,'"
					+ modul + "', '" + nummer + "'," + rs.getString(1) + ");";
		}

		stmt.executeUpdate(query);
		pool.putConnection(con);
	}

	public static synchronized void insertIntoKurs(String modul, Integer nummer,
			String berufsfeld) throws PoolException, SQLException {
		Connection con = pool.getConnection();
		Statement stmt = con.createStatement();

		berufsfeld = berufsfeld.substring(0, 1).toUpperCase()
				+ berufsfeld.substring(1);

		String	query = "INSERT INTO `santisfeedback`.`kurs` (`KursID`, `Modul`, `ModulNummer`, `FK_Berufsfeld`) VALUES (NULL,'"
					+ modul + "', '" + nummer + "'," + berufsfeld + ");";
		stmt.executeUpdate(query);


		pool.putConnection(con);
	}


	public static synchronized void insertIntoKlickFeedback(int bezID, int bew, 
			String bem, int feedbackid) throws PoolException, SQLException {
		Connection con = pool.getConnection();
		Statement stmt = con.createStatement();

		int sehrgut = 0;
		int gut = 0;
		int genuegend = 0;
		int ungenuegend = 0;

		if (0 == bew) {
			sehrgut = 1;
		} else if (1 == bew) {
			gut = 1;
		} else if (2 == bew) {
			genuegend = 1;
		} else if (3 == bew) {
			ungenuegend = 1;
		}

		String query3 = "INSERT INTO `santisfeedback`.`klickfeedback`( `SehrGut`, `Gut`, `genuegend`, `ungenuegend`, `Bemerkung`, `FK_Feedback`, `FK_Bezeichnung`) "
				+ "VALUES ('"
				+ sehrgut
				+ "','"
				+ gut
				+ "','"
				+ genuegend
				+ "','"
				+ ungenuegend
				+ "','"
				+ bem
				+ "','"
				+ feedbackid
				+ "','" + bezID + "')";

		System.out.println(query3);
		stmt.executeUpdate(query3);
		pool.putConnection(con);
	}
	public static synchronized int insertIntoFeedback(String waswargut, String waswarwenigergut,
		String wuensche, String bemerkung, int teilnahme) throws PoolException, SQLException {
		Connection con = pool.getConnection();
		Statement stmt = con.createStatement();
		int lastid = 0;

		String query = "INSERT INTO `santisfeedback`.`feedback`(`FeedbackID`, `WasWarGut`, `WasWarWenigerGut`, `Wuensche`, `Bemerkung`, `FK_Teilnahme`, `FK_Kommentar`) "
				+ "VALUES (NULL,'"
				+ waswargut
				+ "','"
				+ waswarwenigergut
				+ "','" + wuensche + "','" + bemerkung + "','" + teilnahme + "', '1')";

		stmt.executeUpdate(query);

		ResultSet generatedKeys = stmt.executeQuery("select * from feedback");
		if (generatedKeys.last()) {
			lastid = generatedKeys.getInt("FeedbackID");
		}
		pool.putConnection(con);

		return lastid;
	}

	public static synchronized ResultSet auswaehlen(String feld, String tabelle)
			throws PoolException, SQLException {

		String query = "SELECT " + feld + " FROM " + tabelle + ";";
		System.out.println(query);
		Connection con = pool.getConnection();
		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(query);
		pool.putConnection(con);
		return rs;

	}

	public static synchronized ResultSet auswaehlen(String feld, String tabelle, String wo)
			throws PoolException, SQLException {

		String query = "SELECT " + feld + " FROM " + tabelle + " WHERE " + wo
				+ ";";
		System.out.println(query);
		Connection con = pool.getConnection();
		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(query);
		pool.putConnection(con);
		return rs;

	}

	public static synchronized int insertIntoDurchfuehrung(String start, String end,
			String kurs) throws PoolException, SQLException {
		Connection con = pool.getConnection();
		Statement stmt = con.createStatement();
		int lastid = 0;

		String query = "SELECT KursID FROM `santisfeedback`.`kurs` WHERE `Modul`='"
				+ kurs + "';";
		ResultSet rs = stmt.executeQuery(query);

		if (rs.next()) {
			query = "INSERT INTO `santisfeedback`.`durchfuehrung` (`Von`, `Bis`, `FK_Kurs`) VALUES ('"
					+ start + "', '" + end + "', " + rs.getString(1) + ");";
		}
		stmt.executeUpdate(query);

		ResultSet generatedKeys = stmt
				.executeQuery("select * from durchfuehrung");
		if (generatedKeys.last()) {
			lastid = generatedKeys.getInt("durchfuehrungID");
		}

		pool.putConnection(con);

		return lastid;
	}

	public static synchronized void update(String feld, String tabelle, String wo, String was)
			throws PoolException, SQLException {

		String query = "UPDATE " + tabelle + " SET " + feld + "=" + was
				+ " WHERE " + wo + ";";
//		System.out.println(query);
		Connection con = pool.getConnection();
		Statement stmt = con.createStatement();
		stmt.executeUpdate(query);
		pool.putConnection(con);

	}

	public static synchronized int insertIntoTeilnahme(String name, String vorname,
			String duechfuehrungid) throws PoolException, SQLException {
		Connection con = pool.getConnection();
		Statement stmt = con.createStatement();
		int lastid = 0;
		
		String query = "SELECT PersonID FROM `santisfeedback`.`person` WHERE `Name`='"
				+ name + "'AND `Vorname`='" + vorname + "';";
		ResultSet rs = stmt.executeQuery(query);

		if (rs.next()) {
			query = "INSERT INTO `santisfeedback`.`teilnahme` (`FK_Person`, `FK_Durchfuehrung`) VALUES ('"
					+ rs.getString(1) + "', " + duechfuehrungid + ");";
		}
		stmt.executeUpdate(query);
		
		ResultSet generatedKeys = stmt.executeQuery("select * from teilnahme");
        if(generatedKeys.last()){
            lastid = generatedKeys.getInt("TeilnahmeID");    
        }
		
		pool.putConnection(con);
		
		return lastid;
	}

	public static synchronized void insertIntoAuswertung(String kursname, String von,
			String bis, int stoff, int umfang, int unterlagen, int vermittlung,
			int betreuung, String durchfuehrungID) throws PoolException, SQLException {
		Connection con = pool.getConnection();
		Statement stmt = con.createStatement();
		String query = "INSERT INTO `santisfeedback`.`auswertung` (`auswertungID`, `kursname`, `von`, `bis`, `stoff`, `umfang`, `unterlagen`, `vermittlung`, `betreuung`, `FK_Durchfuehrung`)"
				+ " VALUES (NULL, '" + kursname + "' , '"
				+ von + "' , '" + bis + "' ,"
				+ stoff + "," + umfang + ","
				+ unterlagen + "," + vermittlung + "," 
				+ betreuung + "," + durchfuehrungID +");";
		System.out.println(query);
		stmt.executeUpdate(query);
		pool.putConnection(con);
		
	}

}
